﻿CREATE FUNCTION [dbo].[fn_HEXCONV] (@HEXVAL AS VARCHAR(25)) RETURNS DECIMAL(20,0) 
AS BEGIN 
/* Declarations OF Variables Two Decimal VALUES TO Store The Intermdeiate & Final Result,
String Value TO Store The Hexadecimal Value During The Process,Two Counter Variables*/
DECLARE @position int, @INTVAL INT , @CMDSTR NVARCHAR( 255 ) ,@DECVAL DECIMAL(20,0),@DECVALUE DECIMAL(20,0) 
/* Initialising Variables */
SET @position = 1 
SET @DECVAL=0 
WHILE @position <= DATALENGTH(REVERSE(@HEXVAL)) /* Looping Through The String Until It Reaches The 0th Position */
	BEGIN 
		/* Store The Decimal Value IF the Hexa Value IS BETWEEN A-F */
		SET @CMDSTR=CASE UPPER(SUBSTRING(REVERSE(@HEXVAL) ,@position,1)) WHEN 'A' THEN '10' WHEN 'B' THEN '11' WHEN 'C' THEN '12' WHEN 'D' THEN '13' WHEN 'E' THEN '14' WHEN 'F' THEN '15' ELSE SUBSTRING(REVERSE(@HEXVAL) ,@position,1) END 
		SET @INTVAL=CAST(@CMDSTR as INT) /* Casting The String TO Integer */
		SET @DECVALUE=@INTVAL						
		SET @DECVAL=@DECVAL+((@DECVALUE)*POWER(CAST(16 AS BIGINT),@position-1))/* Finding The Corresponding Decimal Value & Adding it TO The Result */ 
		SET @position=@position+1 /* Incrementing The Counter */
	END 
	RETURN CAST(@DECVAL as Decimal(20,0))				/* Return The Converted Decimal Value Back */
End
